using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Text;
using System.Windows.Forms;

namespace Shraga.Client
{
	/// <summary>
	/// Export data to Access-*.mdb file.
	/// </summary>
	public class PsychologyExport
	{
		#region Class Members

		private DataTable m_dtMalshabs;

		#endregion

		#region Constructors

		/// <summary>
		/// Updates the malshabs' table, defined in the db mentioned in DataSourcePath,
		/// with the malshabs' details that are found in the DataTable received
		/// </summary>
		/// <param name="dtMalshabs"> The dataTable contains the malshab's
		///  ID Number, First Name, Last Name, Sex, Kaba, Dapar, Zadak </param>
		public PsychologyExport(DataTable dtMalshabs)
		{
			m_dtMalshabs = dtMalshabs;
		}

		#endregion

		#region Methods

		/// <summary>
		/// Insert the data in the dataTable into the Access file
		/// </summary>
		/// <returns> A FileStream of the Access file </returns>
		public void ExportToFile(string FullFilePath)
		{
			OleDbCommand newCom = new OleDbCommand();
			OleDbConnection conn = new System.Data.OleDb.OleDbConnection();

			try
			{ 
				conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" + 
					@"Data source = " + FullFilePath;
				newCom.Connection = conn;
				if (conn.State == ConnectionState.Closed) conn.Open();

				string sSQL = "DELETE FROM IturData";
				newCom.CommandText = sSQL;
				newCom.ExecuteNonQuery();

				for (int i = 0; i < m_dtMalshabs.Rows.Count; i++)
				{
					sSQL = "INSERT INTO IturData values(";
					//in order: Sex, ID Number, Last Name, First Name, Dapar, Kaba, Zadak
					sSQL = sSQL + "'" + m_dtMalshabs.Rows[i]["Sex"].ToString().Substring(0,1) + "', ";
					sSQL = sSQL + "'" + m_dtMalshabs.Rows[i]["CANDIDATE_ID"].ToString() + "', ";
					sSQL = sSQL + "'" + m_dtMalshabs.Rows[i]["Lastname"].ToString().Replace("'","") + "', ";
					sSQL = sSQL + "'" + m_dtMalshabs.Rows[i]["Firstname"].ToString().Replace("'","") + "', ";
					sSQL = sSQL + ((m_dtMalshabs.Rows[i]["Dapar"] != DBNull.Value) ? m_dtMalshabs.Rows[i]["Dapar"].ToString() : "NULL") + ", ";
					sSQL = sSQL + ((m_dtMalshabs.Rows[i]["Kaba"] != DBNull.Value) ? "'" + m_dtMalshabs.Rows[i]["Kaba"].ToString() + "'" : "NULL") + ", ";
					sSQL = sSQL + ((m_dtMalshabs.Rows[i]["Zadak"] != DBNull.Value) ? "'" + m_dtMalshabs.Rows[i]["Zadak"].ToString() + "'" : "NULL") + ")";
					newCom.CommandText = sSQL;
					newCom.ExecuteNonQuery();
				}
			}
			catch (Exception ex)
			{
				throw;
			}
			finally
			{
				conn.Close();
			}
		}        	

		#endregion
	}
}